﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using HYI.SDBSERVER;
using System.IO;
using OfficeOpenXml;
using Sunny.UI;

namespace SDBSERVER
{
    public partial class FMINPUT : Form
    {
        public string _INPUTTABLE = string.Empty; //导入表名
        public string _USERNO = string.Empty; //用户编号

        public FMINPUT()
        {
            InitializeComponent();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void uiTxtFile_Click(object sender, EventArgs e)
        {
            //弹窗选择文件窗口
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.InitialDirectory = System.Environment.CurrentDirectory;
            fileDialog.Multiselect = true;
            fileDialog.Title = "请选择文件";
            fileDialog.Filter = "所有文件(*xlsx*)|*.xlsx*"; //设置要选择的文件的类型
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                uiTxtFile.Text = fileDialog.FileName;//返回文件的完整路径                
            }
        }

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(string fullFielPath)
        {
            try
            {
                FileInfo existingFile = new FileInfo(fullFielPath);

                ExcelPackage package = new ExcelPackage(existingFile);
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//选定 指定页

                return WorksheetToTable(worksheet);
            }
            catch (Exception ex)
            {
                Console.WriteLine("发送错误:" + ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }

        private static string GetString(object obj)
        {
            try
            {
                return obj.ToString();
            }
            catch (Exception ex)
            {
                return "";
            }
        }

        private void FMINPUT_Load(object sender, EventArgs e)
        {
            uiTabINPUT.TabVisible = false;
            btnBack.Top = btnSet.Top;
            btnBack.Left = btnSet.Left;
        }

        private void btnSet_Click(object sender, EventArgs e)
        {
            //导入
            string sExcelFile = uiTxtFile.Text;
            if (File.Exists(sExcelFile))
            {
                DataTable myDATA = WorksheetToTable(sExcelFile);
                if (myDATA.Rows.Count == 0)
                {
                    Console.WriteLine("没有数据，走");
                    return;
                }

                //2.读取本地表字段
                DBSERVER myADO = new DBSERVER();

                string sFieldList = string.Empty ;
                string SQL2 = "select * from " + _INPUTTABLE
                 + " where 1=2";
                DataTable myField = myADO.query(SQL2);
                if((myField is null) || (myField.Rows.Count == 0))
                {
                    Console.WriteLine("查询报错，走");
                    return;
                }

                if ((myDATA.Columns.Count + 1) != myField.Columns.Count)
                {
                    Console.WriteLine("列个数不符号要求，不处理");
                    return;
                }

                myADO.SQLList.Clear();

                SQL2 = "delete from " + _INPUTTABLE + " where F000='" + _USERNO + "'";
                myADO.SQLList.Add(SQL2);

                for (int j = 0; j < myField.Columns.Count; j++)
                {
                    sFieldList = sFieldList + myField.Columns[j].ColumnName + ",";
                }
                sFieldList = sFieldList.Substring(0, sFieldList.Length - 1);

                //3.将excel数据插入数据库
                for (int k = 0; k < myDATA.Rows.Count; k++)
                {
                    string SQL3 = "insert into " + _INPUTTABLE
                     + "(" + sFieldList + ") "
                     + "values('" + _USERNO + "'";

                    for (int z = 0; z < myDATA.Columns.Count; z++)
                    {
                        SQL3 = SQL3 + ",'" + myDATA.Rows[k][z].ToString() + "'";
                    }
                    SQL3 = SQL3 + ")";

                    myADO.SQLList.Add(SQL3);
                }

                bool IsOk;
                string sError = string.Empty;
                myADO.doSave(out IsOk, out sError);
                if (IsOk)
                {
                    SQL2 = "exec proc_import_center '" + _USERNO + "','1','','" + _INPUTTABLE + "'";
                    Console.WriteLine(SQL2);
                    
                    DataTable myresult = myADO.query(SQL2);
                    try
                    {
                        uiDGINPUT.DataSource = myresult;
                        uiDGINPUT.AllowUserToAddRows = false;
                        if (uiDGINPUT.Columns.Count > 0)
                        {
                            uiDGINPUT.Columns[0].Width = uiDGINPUT.Width-80;
                        }
                        uiTabINPUT.SelectedTab = uiTabINPUT.TabPages[1];
                        btnSet.Visible = false;
                        btnBack.Visible = true;
                    }
                    catch (Exception ex) {
                        this.ShowErrorDialog(ex.Message); }
                }
            }
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            btnBack.Visible = false;
            btnSet.Visible = true;
            uiTabINPUT.SelectedTab = uiTabINPUT.TabPages[0];
        }

        

     }
    }
